Taeluf.com

Databasing.php

<?php

namespace Tlf\Tester;

trait Databasing {

    public function getPdo($dbName = ':memory:'){
        $pdo = new \PDO('sqlite:'.$dbName);
        return $pdo;
    }

    public function dbInsert($pdo, $tableName, $values){

        $cols = [];
        $binds = [];
        foreach ($values as $key=>$value){
            $cols[] = $key;
            $binds[":{$key}"] = $value;
        }
        $colsStr = '`'.implode('`, `',$cols).'`';
        $bindsStr = implode(', ', array_keys($binds));
        $query = "INSERT INTO `${tableName}`(${colsStr}) 
                VALUES (${bindsStr})
            ";
        $stmt = $pdo->prepare($query);
        if ($stmt===false){
            echo "PDO ErrorInfo:\n";
            print_r($pdo->errorInfo());
            // echo "\n";
            // return;
            throw new \Exception("Could not insert values into databse.");
        } 
        $stmt->execute($binds);
    }

    /**
     * @param array $cols array of columns like: `['col_name'=>'VARCHAR(80)', 'col_two'=> 'integer']`
     */
    public function createTable(\PDO $pdo, string $tableName, array $cols, bool $dropIfExists=false){
        $colStatements = [];
        foreach ($cols as $col => $definition){
            $statement = '`'.$col.'` '. $definition;
            $colStatements[] = $statement;
        }
        $colsSql = implode(", ", $colStatements);
        $drop = $dropIfExists ? "DROP TABLE IF EXISTS `{$tableName}`;\n" : '';
        $sql =
        <<<SQL
            {$drop}
            CREATE TABLE `{$tableName}`
            (
            {$colsSql}
            )
            ;
            
        SQL;

        $this->dbExec($pdo, $sql);
    }
    /**
     * Returns the first row from the query result.
     * @todo don't fetchAll(). Only fetch first row.
     */
    public function queryOne(\PDO $pdo, string $sql, ?array $binds=null): ?array{
        $rows = $this->query($pdo, $sql, $binds);
        return $rows[0] ?? null;
    }
    public function query(\PDO $pdo, string $sql, ?array $binds=null): array{
        $pdo = $pdo;
        $stmt = $pdo->prepare($sql);
        if ($stmt===false){
            var_dump($pdo->errorInfo());
            throw new \Exception("Sql problem.");
        }
        $stmt->execute($binds);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        return $rows;
    }

    public function dbExec($pdo, $sql, $binds=[]){
        $stmt = $pdo->prepare($sql);
        $stmt->execute($binds);
    }
}